<?php
namespace lib\Db;

class Mysql {
	private $_aServer = array ();
	private $_aMaster = array ();
	private $_aSlave = array ();
	private $_rResult;
	
	public function __construct($aConf) {
		$this->connect ( $aConf );
	}
	
	public function connect($aConf, $sServer = 'db') {
		try {
			$sDsn = 'mysql:host=' . $aConf ['host'] . ';dbname=' . $aConf ['name'];
			$oDbh = new \PDO ( $sDsn, $aConf ['user'], $aConf ['password'] );
			$oDbh->setAttribute ( \PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION );
			$oDbh->setAttribute ( \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true );
			$sCharset = $aConf ['charset'] ? $aConf ['charset'] : 'utf8';
			$oDbh->exec ( 'SET NAMES ' . $sCharset );
			$aConf ['table'] = array ();
			$this->_aServer [$sServer] = $aConf;
			if ($aConf ['slave'] && $this->_aMaster [$sServer]) {
				$this->_aSlave [$sServer] [] = $oDbh;
			} else {
				$this->_aMaster [$sServer] = $oDbh;
			}
			return $oDbh;
		} catch ( \Exception $e ) {
		}
	}
	
	public function exec($sSql, $aValue = null, $sServer = 'db') {
		try {
			$oServer = $this->_aMaster [$sServer];
			$rResult = $oServer->prepare ( $sSql );
			if (is_array ( $aValue )) {
				$i = 1;
				foreach ( $aValue as $sKey => $val ) {
					if (is_int ( $sKey )) {
						$rResult->bindParam ( $i ++, $aValue [$sKey] );
					} else {
						$rResult->bindParam ( ':' . $sKey, $aValue [$sKey] );
					}
				}
			} elseif ($aValue !== null) {
				$rResult->bindParam ( 1, $aValue );
			}
			$rResult->execute ();
			return $rResult->rowCount ();
		} catch ( \Exception $e ) {
			$e->getMessage ();
		}
		return false;
	}
	
	public function query($sSql, $aValue = null, $sServer = 'db') {
		$this->_rResult = null;
		
		try {
			$oServer = $this->_getSlave ( $sServer, false );
			$rResult = $oServer->prepare ( $sSql );
			if (is_array ( $aValue )) {
				$i = 1;
				foreach ( $aValue as $sKey => $val ) {
					if (is_int ( $sKey )) {
						$rResult->bindParam ( $i ++, $aValue [$sKey] );
					} else {
						$rResult->bindParam ( ':' . $sKey, $aValue [$sKey] );
					}
				}
			} elseif ($aValue !== null) {
				$rResult->bindParam ( 1, $aValue );
			}
			$rResult->execute ();
			$this->_rResult = $rResult;
			return $rResult;
		} catch ( \Exception $e ) {
			echo $sSql;
			echo $e->getMessage ();
		}
		return false;
	}
	
	public function getRow($rResult = null, $sModel = 'assoc') {
		if ($rResult === null) {
			$rResult = $this->_rResult;
		}
		
		if ($rResult === null) {
			return false;
		}
		
		try {
			switch (strtolower ( $sModel )) {
				case 'assoc' :
					$sModel = \PDO::FETCH_ASSOC;
					break;
				case 'num' :
					$sModel = \PDO::FETCH_NUM;
					break;
				default :
					$sModel = \PDO::FETCH_BOTH;
			}
			return $rResult->fetch ( $sModel );
		} catch ( \Exception $e ) {
			$e->getMessage ();
		}
		
		return false;
	}
	
	private function _getSlave($sServer) {
		$iCount = count ( $this->_aSlave [$sServer] );
		if ($iCount > 0) {
			$i = floor ( rand ( 0, $iCount ) );
			return $this->_aSlave [$sServer] [$i];
		} else {
			return $this->_aMaster [$sServer];
		}
	}
	
	public function getOne($sSql, $aValue = null, $sServer = 'db') {
		$this->query ( $sSql, $aValue, $sServer );
		$aRow = $this->getRow ();
		if ($aRow) {
			if (count ( $aRow ) == 1) {
				return $aRow [key ( $aRow )];
			} else {
				return $aRow;
			}
		} else {
			return false;
		}
	}
	
	public function getTableInfo($sTable, $sServer = 'db') {
		if (! array_key_exists ( $sTable, $this->_aServer [$sServer] ['table'] )) {
			$sSql = 'DESC `' . $this->_aServer [$sServer] ['pre'] . $sTable . '`';
			$this->query ( $sSql, null, $sServer );
			while ( ($aRow = $this->getRow ()) !== false ) {
				$this->_aServer [$sServer] ['table'] [$sTable] ['field'] [$aRow ['Field']] = $aRow;
				if ($aRow ['Key'] == 'PRI') {
					$this->_aServer [$sServer] ['table'] [$sTable] ['pri'] [] = $aRow ['Field'];
				}
			}
			
			list ( $sKey ) = each ( $this->_aServer [$sServer] ['table'] [$sTable] ['field'] );
			list ( $sPre ) = explode ( '_', $sKey );
			$this->_aServer [$sServer] ['table'] [$sTable] ['pre'] = $sPre ? $sPre . '_' : '';
		}
		return $this->_aServer [$sServer] ['table'] [$sTable];
	}
	
	public function getArray($sTable, $sKey = 'id', $sName = 'name', $aWhere = array(), $aSort = array(), $sServer = 'db') {
		$aTableInfo = $this->getTableInfo ( $sTable, $sServer );
		$sSql = 'SELECT ' . $aTableInfo ['pre'] . $sKey . ' id,' . $aTableInfo ['pre'] . $sName . ' name FROM `' . $sTable . '`';
		$sGroup = ' GROUP BY ' . $aTableInfo ['pre'] . $sKey;
		$aSqlWhere = array ();
		$aVaule = array ();
		$aArray = array ();
		if (is_array ( $aWhere )) {
			foreach ( $aWhere as $sKey => $sRow ) {
				if (array_key_exists ( $aTableInfo ['pre'] . $sKey, $aTableInfo ['field'] )) {
					$sValue = trim ( $sRow );
					if (preg_match ( '/^(<>|>|<)(.+?)$/i', $sValue, $aMatch )) {
						$aSqlWhere [] = $aTableInfo ['pre'] . $sKey . $aMatch [1] . ' ?';
						$aVaule [] = trim ( $aMatch [2] );
					} else {
						$aSqlWhere [] = $aTableInfo ['pre'] . $sKey . ' = ?';
						$aVaule [] = $sRow;
					}
				}
			}
			if (! empty ( $aSqlWhere )) {
				$sSql .= ' WHERE ' . implode ( ' AND ', $aSqlWhere );
			}
		}
		$sSql .= $sGroup;
		if (! empty ( $aSort )) {
			$aSqlSort = array ();
			foreach ( $aSort as $sRow ) {
				list ( $sSortField, $sSortType ) = explode ( ':', $sRow );
				if ($sSortType) {
					$aSqlSort [] = $aTableInfo ['pre'] . $sSortField . ' ' . $sSortType;
				} else {
					$aSqlSort [] = $aTableInfo ['pre'] . $sSortField;
				}
			}
			$sSql .= ' ORDER BY ' . implode ( ',', $aSqlSort );
		} elseif (in_array ( $aTableInfo ['pre'] . 'sort', $aTableInfo ['field'] )) {
			$sSql .= ' ORDER BY ' . $aTableInfo ['pre'] . 'sort';
		} else {
			$sSql .= ' ORDER BY ' . $aTableInfo ['pre'] . $sName;
		}
		$aData = $this->query ( $sSql, $aVaule );
		while ( ($aRow = $this->getRow ()) !== false ) {
			$aArray [$aRow ['id']] = $aRow ['name'];
		}
		return $aArray;
	}
	
	public function ls($aParam) {
		$aParam ['server'] = $aParam ['server'] ? $aParam ['server'] : 'db';
		$sSql = 'SELECT * FROM ';
		$sSql .= ' ' . $aParam ['table'];
		$aTableInfo = $this->getTableInfo ( $aParam ['table'], $aParam ['server'] );
		if ($aParam ['key'] && array_key_exists ( $aTableInfo ['pre'] . $aParam ['key'], $aTableInfo ['field'] )) {
			$sReturnKey = $aParam ['key'];
		} else {
			$sReturnKey = '';
		}
		$aValue = array ();
		$aSqlWhere = array ();
		if (is_array ( $aParam ['where'] )) {
			foreach ( $aParam ['where'] as $sKey => $sRow ) {
				if (array_key_exists ( $aTableInfo ['pre'] . $sKey, $aTableInfo ['field'] )) {
					if (! is_array ( $sRow )) {
						$sValue = trim ( $sRow );
					}
					if (is_array ( $sRow )) {
						$aSqlWhere [] = $aTableInfo ['pre'] . $sKey . ' IN (?' . str_repeat ( ', ?', count ( $sRow ) - 1 ) . ')';
						$aValue = array_merge ( $aValue, $sRow );
					} elseif (preg_match ( '/^(<>|>=|<=|>|<)(.+?)$/', $sValue, $aMatch )) {
						$aSqlWhere [] = $aTableInfo ['pre'] . $sKey . $aMatch [1] . ' ?';
						$aValue [] = trim ( $aMatch [2] );
					} elseif (substr ( $sValue, 0, 1 ) == '%' || substr ( $sValue, - 1 ) == '%') {
						$aSqlWhere [] = $aTableInfo ['pre'] . $sKey . ' LIKE ?';
						$aValue [] = $sValue;
					} else {
						$aSqlWhere [] = $aTableInfo ['pre'] . $sKey . ' = ?';
						$aValue [] = $sRow;
					}
				} elseif (strtolower ( $sKey ) == 'where') {
					$aSqlWhere [] = $sRow;
				}
			}
		}
		if ($aSqlWhere) {
			$sSql .= ' WHERE ' . implode ( ' AND ', $aSqlWhere );
		}
		
		$iCount = $this->_getItems ( $sSql, $aValue, $aParam ['server'] );
		$this->query ( $sSql, $aValue );
		while ( ($aRow = $this->getRow ()) !== false ) {
			$aOne = array ();
			foreach ( $aRow as $sKey => $sValue ) {
				$sKey = preg_replace ( '/^' . $aTableInfo ['pre'] . '/', '', $sKey );
				$aOne [$sKey] = $sValue;
			}
			if ($aOne [$sReturnKey]) {
				$aData ['data'] [$aOne [$sReturnKey]] = $aOne;
			} else {
				$aData ['data'] [] = $aOne;
			}
		}
		$aData ['count'] = $iCount;
		return $aData;
	}
	
	private function _getItems($sSql, $aValue, $sServer) {
		$sSql = preg_replace ( '/[\r\n]+/', ' ', $sSql );
		$sSql = preg_replace ( '/(select).*[\s\r\n]+(from.*)/is', '$1 COUNT(1) $2', $sSql );
		$iPos = strripos ( $sSql, 'ORDER BY' );
		if ($iPos !== false) {
			$sSql = substr ( $sSql, 0, $iPos );
		}
		$this->query ( $sSql, $aValue, $sServer );
		if (strripos ( $sSql, 'GROUP BY' )) {
			$rows = $this->getRows ();
			return count ( $rows );
		} else {
			return $this->getResult ();
		}
	}
	
	public function getRows($rResult = null, $sModel = 'assoc') {
		if ($rResult === null) {
			$rResult = $this->_rResult;
		}
		
		if ($rResult === null) {
			return false;
		}
		
		try {
			switch (strtolower ( $sModel )) {
				case 'assoc' :
					$sModel = \PDO::FETCH_ASSOC;
					break;
				case 'num' :
					$sModel = \PDO::FETCH_NUM;
					break;
				default :
					$sModel = \PDO::FETCH_BOTH;
			}
			return $rResult->fetchAll ( $sModel );
		} catch ( \Exception $e ) {
			$e->getMessage ();
		}
		return false;
	}
	
	public function getResult($iIndex = 0, $rResult = null) {
		if ($rResult === null) {
			$rResult = $this->_rResult;
		}
		
		if ($rResult === null) {
			return false;
		}
		
		return $rResult->fetchColumn ( $iIndex );
	}
	
	public function clear($sTable, $sServer = 'db') {
		$aTableInfo = $this->getTableInfo ( $sTable, $sServer );
		if ($aTableInfo) {
			$sSql = 'TRUNCATE ' . $this->_aServer [$sServer] ['pre'] . $sTable;
			$this->exec ( $sSql, null, $sServer );
			return true;
		} else {
			return false;
		}
	}
	
	public function insert($sTable, $aData, $sServer = 'db') {
		$aTableInfo = $this->getTableInfo ( $sTable, $sServer );
		if ($aTableInfo) {
			$sTable = $this->_aServer [$sServer] ['pre'] . $sTable;
			$i = 0;
			foreach ( $aData as $sKey => $sRow ) {
				if (array_key_exists ( $aTableInfo ['pre'] . $sKey, $aTableInfo ['field'] )) {
					if ($i == 0) {
						$sField = '`' . $aTableInfo ['pre'] . $sKey . '`';
						$sValue = '?';
					} else {
						$sField .= ', `' . $aTableInfo ['pre'] . $sKey . '`';
						$sValue .= ', ?';
					}
					$aValue [] = $sRow;
					$i ++;
				}
			}
			$sSql = 'INSERT INTO `' . $sTable . '` (' . $sField . ') VALUES (' . $sValue . ')';
			if ($this->exec ( $sSql, $aValue, $sServer )) {
				return $this->getInsertId ( $sServer );
			} else {
				return false;
			}
		} else {
			return false;
		}
	}
	
	public function getInsertId($sServer = 'db') {
		$oServer = $this->_aMaster [$sServer];
		return $oServer->lastInsertId ();
	}
	
	public function update($sTable, $aData, $aWhere = array(), $sServer = 'db') {
		$aTableInfo = $this->getTableInfo ( $sTable, $sServer );
		if ($aTableInfo) {
			$sTable = $this->_aServer [$sServer] ['pre'] . $sTable;
			$i = 0;
			foreach ( $aData as $sKey => $sRow ) {
				if (array_key_exists ( $aTableInfo ['pre'] . $sKey, $aTableInfo ['field'] )) {
					$aTableData [$aTableInfo ['pre'] . $sKey] = $sRow;
					if ($i == 0) {
						$sField = '`' . $aTableInfo ['pre'] . $sKey . '` = ?';
					} else {
						$sField .= ', `' . $aTableInfo ['pre'] . $sKey . '` = ?';
					}
					$aValue [] = $sRow;
					$i ++;
				}
			}
			$sSql = 'UPDATE `' . $sTable . '`  SET ' . $sField;
			if ($aWhere) {
			} elseif (count ( $aTableInfo ['pri'] ) == 1 && array_key_exists ( $aTableInfo ['pri'] [0], $aTableData )) {
				$sSql .= ' WHERE ' . $aTableInfo ['pri'] [0] . ' = ?';
				$aValue [] = $aTableData [$aTableInfo ['pri'] [0]];
			}
			return $this->exec ( $sSql, $aValue, $sServer );
		} else {
			return false;
		}
	}
	
	public function get($sTable, $iId, $sServer = 'db') {
		$aTableInfo = $this->getTableInfo ( $sTable, $sServer );
		if ($aTableInfo) {
			$sTable = $this->_aServer [$sServer] ['pre'] . $sTable;
			if (is_array ( $iId )) {
				foreach ( $iId as $sKey => $sRow ) {
					if (array_key_exists ( $aTableInfo ['pre'] . $sKey, $aTableInfo ['field'] )) {
						if (is_array ( $sRow )) {
							$aWhere [] = $aTableInfo ['pre'] . $sKey . ' IN (?' . str_repeat ( ',?', count ( $sRow ) - 1 ) . ')';
							$aVaule = array_merge ( $aVaule, $sRow );
						} else {
							$sValue = trim ( $sRow );
							if (preg_match ( '/^(<>|>|<|like)(.+?)$/i', $sValue, $aMatch )) {
								$aWhere [] = $aTableInfo ['pre'] . $sKey . $aMatch [1] . ' ?';
								$aVaule [] = trim ( $aMatch [2] );
							} else {
								$aWhere [] = $aTableInfo ['pre'] . $sKey . ' = ?';
								$aVaule [] = $sRow;
							}
						}
					
					}
				}
			} elseif (count ( $aTableInfo ['pri'] ) == 1) {
				$aWhere [] = $aTableInfo ['pri'] [0] . ' = ?';
				$aVaule = $iId;
			} else {
				return false;
			}
			$sSql = 'SELECT * FROM `' . $sTable . '` WHERE ' . implode ( ' AND ', $aWhere );
			$aData = $this->getOne ( $sSql, $aVaule, $sServer );
			if (is_array ( $aData )) {
				foreach ( $aData as $sKey => $sRow ) {
					unset ( $aData [$sKey] );
					$sKey = preg_replace ( '/^' . $aTableInfo ['pre'] . '/', '', $sKey );
					$aData [$sKey] = $sRow;
				}
			}
			return $aData;
		}
		
		return false;
	}
	
	public function delete($sTable, $iId, $sServer = 'db') {
		$aTableInfo = $this->getTableInfo ( $sTable, $sServer );
		if ($aTableInfo) {
			$sTable = $this->_aServer [$sServer] ['pre'] . $sTable;
			if (is_array ( $iId )) {
				foreach ( $iId as $sKey => $sRow ) {
					if (array_key_exists ( $aTableInfo ['pre'] . $sKey, $aTableInfo ['field'] )) {
						if (is_array ( $sRow )) {
							$aWhere [] = $aTableInfo ['pre'] . $sKey . ' IN (?' . str_repeat ( ',?', count ( $sRow ) - 1 ) . ')';
							$aVaule = array_merge ( $aVaule, $sRow );
						} else {
							$sValue = trim ( $sRow );
							if (preg_match ( '/^(<>|>|<|like)(.+?)$/i', $sValue, $aMatch )) {
								$aWhere [] = $aTableInfo ['pre'] . $sKey . $aMatch [1] . ' ?';
								$aVaule [] = trim ( $aMatch [2] );
							} else {
								$aWhere [] = $aTableInfo ['pre'] . $sKey . ' = ?';
								$aVaule [] = $sRow;
							}
						}
					
					}
				}
			} elseif (count ( $aTableInfo ['pri'] ) == 1) {
				$aWhere [] = $aTableInfo ['pri'] [0] . ' = ?';
				$aVaule = $iId;
			} else {
				return false;
			}
			$sSql = 'DELETE FROM `' . $sTable . '` WHERE ' . implode ( ' AND ', $aWhere );
			return $this->exec ( $sSql, $aVaule, $sServer );
		}
		
		return false;
	}
	
	private function _getWhere($sPre, $aField, $aWhere) {
		$aSqlWhere = array ();
	}
}

?>